SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[WeeklyGetTrades] 
@FundID VARCHAR(10)
, @TradeType VARCHAR(50)
, @StartDate Date = NULL
, @EndDate Date = NULL


AS


BEGIN
	SET @EndDate = CASE WHEN @EndDate = NULL THEN GETDATE()	 ELSE @EndDate  END
	SET @StartDate = CASE WHEN @StartDate = NULL THEN GETDATE()-7 ELSE @StartDate END
	
	DECLARE @ExportID INT
	SET @ExportID =  (SELECT MAX(ex.ExportID) FROM Reporting.Export ex WHERE NAVDate = @EndDate AND FundID = @FundID)
					

IF (@TradeType IN ('New', 'Added') )
BEGIN
	
DECLARE @Temp TABLE (
	InstName VARCHAR(MAX),
	InstType VARCHAR(MAX	)
	, Par DECIMAL(36,10) 
	, TradePrice DECIMAL(36,10) 
	, LevYield DECIMAL(36,10) 
	, Portfolio VARCHAR(MAX	)
	, New INTEGER
	)
INSERT INTO @Temp



SELECT     i.InstrName
          , MAX(id.InstTypeDescr)
     
          , ABS(SUM(t.TradeAmount)) 
          , SUM(ABS(t.TradeAmount) * t.TradePrice) / SUM(ABS(t.TradeAmount)) 
		, SUM(ABS(t.TradeAmount) * a.GAAPYldPurchPrc*p.LevYldMult) / SUM(ABS(t.TradeAmount)) 
			     , p.PortfolioName
			     , CASE WHEN MAX(CAST(i.CreatedDate AS DATE)) = CAST(@EndDate AS DATE) THEN 1 ELSE 0 END         


 FROM       Reporting.Trade t
            
            INNER JOIN Reporting.Instrument i
            ON         t.ExportId     = i.ExportId
            AND        t.InstrumentId = i.InstrumentId
            INNER JOIN Reporting.Portfolio p
            ON         p.PortfolioId = t.PortfolioId
			AND		p.ExportId = t.ExportId
            INNER JOIN dbo.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
			INNER JOIN  Reporting.Asset a ON  t.LotId = a.LotId AND t.PortfolioId = a.PortfolioId AND  a.InstrumentId = t.InstrumentId          
			AND a.ExportId = t.ExportId
 WHERE      t.ExportId =@ExportID
  AND        t.TradeDate  BETWEEN @StartDate AND @EndDate
 AND        t.TradeType    = 'Purchase'
 
 AND        t.PortfolioId IN
            (SELECT PortfolioId
            FROM    dbo.Portfolio
            WHERE   FundId = @FundID)
 
 GROUP BY   i.InstrName
          , p.PortfolioName
 ORDER BY   i.InstrName


 
 SELECT  InstName ,
         InstType ,
         Par ,
         TradePrice ,
         LevYield ,
         Portfolio
		  FROM  @Temp tmp


	WHERE tmp.New = CASE WHEN @TradeType = 'New' THEN 1 ELSE 0 END	
	ORDER BY tmp.InstName ASC, tmp.Portfolio ASC
	
    
END	


IF (@TradeType = 'Sale' OR @TradeType= 'Paydown' )
BEGIN



 
SELECT     i.InstrName AS InstrName
          , MAX(id.InstTypeDescr) AS instType
          , ABS(SUM(t.TradeAmount)) AS Par
          , SUM(ABS(t.TradeAmount) * t.TradePrice) / SUM(ABS(t.TradeAmount)) AS SalePrice
				  , SUM(ABS(t.TradeAmount) * a.PurchPrc) / SUM(ABS(t.TradeAmount)) AS PurchPrice
			     , p.PortfolioName
			 


 FROM       Reporting.Trade t
            
            INNER JOIN Reporting.Instrument i
            ON         t.ExportId     = i.ExportId
            AND        t.InstrumentId = i.InstrumentId
            INNER JOIN Reporting.Portfolio p
            ON         p.PortfolioId = t.PortfolioId
			AND		p.ExportId = t.ExportId
            INNER JOIN dbo.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
			INNER JOIN  Reporting.Asset a ON  t.LotId = a.LotId AND t.PortfolioId = a.PortfolioId AND  a.InstrumentId = t.InstrumentId          
			AND a.ExportId = t.ExportId
 WHERE      t.ExportId =@ExportID
  AND        t.TradeDate  BETWEEN @StartDate AND @EndDate
 AND        t.TradeType    =@TradeType
 
 AND        t.PortfolioId IN
            (SELECT PortfolioId
            FROM    dbo.Portfolio
            WHERE   FundId = @FundID)
 
 GROUP BY   i.InstrName
          , p.PortfolioName
 ORDER BY   i.InstrName


    
END	

END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Weekly', 'SCHEMA', N'dbo', 'PROCEDURE', N'WeeklyGetTrades', NULL, NULL
GO
